package com.SRMS.service;

import com.SRMS.dao.HistoryOfRoomUsageDAO;
import com.SRMS.dao.StudyRoomDAO;
import com.SRMS.domain.HistoryOfRoomUsage;
import com.SRMS.domain.StudyRoom;
import com.SRMS.utils.JDBCUtilsByDruid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Albert
 * @version 1.0
 * @date 2023/12/19-20:11
 * @describe 管理员管理自习室服务
 */
public class StudyRoomManageService {
    private StudyRoomDAO studyRoomDAO = new StudyRoomDAO();
    private HistoryOfRoomUsageDAO historyOfRoomUsageDAO = new HistoryOfRoomUsageDAO();

    /**
     * 查询所有的自习室使用记录
     * @return 所有自习室的历史使用记录列表
     */
    public List<HistoryOfRoomUsage> historyOfRoomUsageList(){
        return historyOfRoomUsageDAO.queryMulti("SELECT * FROM historyOfRoomUsage;", HistoryOfRoomUsage.class);
    }

    /**
     * 查询所有的自习室信息
     * @return 返回自习室列表
     */
    public List<StudyRoom> studyRoomList(){
        return studyRoomDAO.queryMulti("SELECT * FROM studyRoom", StudyRoom.class);
    }

    /**
     *  删除自习室
     * @param studyRoomId 需要删除的自习室编号
     * @return 删除结果（true/false）
     */
    public boolean deleteStudyRoomById(String studyRoomId){
        int result = 0;
        String sql1 = "DELETE FROM studyRoomUsageRecord WHERE studyRoomId = ?;";
        String sql3 = "DELETE FROM revokeRecord WHERE studyRoomId = ?;";
        String sql4 = "DELETE FROM rightToUseStudyRoom WHERE studyRoomId = ?;";
        String sql2 = "DELETE FROM studyRoom WHERE studyRoomId = ?;";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //使用事务保证数据一致性
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, studyRoomId);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setString(1, studyRoomId);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setString(1, studyRoomId);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1, studyRoomId);
            result = preparedStatement.executeUpdate();

            if(result == 0){
                //撤销回滚
                try {
                    connection.rollback();//默认回滚到事务开始时的状态
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }

                return false;
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //撤销回滚
            try {
                connection.rollback();//默认回滚到事务开始时的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }

            throw new RuntimeException(e);

        } finally {
            JDBCUtilsByDruid.close(null, preparedStatement, connection);
        }

        return true;
    }

    /**
     * 更新自习室信息
     * @param studyRoom 需要进行更新的自习室对象
     * @return 更新结果（true/false）
     */
    public boolean updateStudyRoom(StudyRoom studyRoom){
        int result = studyRoomDAO.update("UPDATE studyRoom SET address = ?, roomName = ?, seats = ?, remainingSeats = ? WHERE studyRoomId = ?;",
                studyRoom.getAddress(), studyRoom.getRoomName(), studyRoom.getSeats(), studyRoom.getRemainingSeats(), studyRoom.getStudyRoomId());
        return result == 1;
    }

    /**
     * 添加自习室
     * @param studyRoom 自习室对象
     * @return 添加结果（true/false）
     */
    public boolean addStudyRoom(StudyRoom studyRoom){
        int result = studyRoomDAO.update("INSERT INTO studyRoom VALUES (?, ?, ?, ?, ?);",studyRoom.getStudyRoomId(),
                studyRoom.getAddress(), studyRoom.getRoomName(), studyRoom.getSeats(), studyRoom.getRemainingSeats());
        return result == 1;
    }
    /**
     * 查询自习室
     * @param studyRoomId 自习室编号
     * @return 查询结果为自习室对象
     */
    public StudyRoom getStudyRoomById(String studyRoomId){
        return studyRoomDAO.querySingle("SELECT * FROM studyRoom WHERE studyRoomId = ?", StudyRoom.class, studyRoomId);
    }
}
